package com.accountant.travelaccountant;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseOperations_NonActivity {

	/* Database Name and Version */
	private static final String DATABASE_NAME = "travelExpenseManager.db";
	private static final int DATABASE_VERSION = 4;

	/* Schema for AmountTable */
	/* Table name */
	private static final String AmountTable = "AmountTable";
	/* Column Names */
	private static final String AmountTableTitle = "AmountTableTitle";
	private static final String AmountTableAmount = "AmountTableAmount";
	private static final String AmountTableTotalMembers = "AmountTableTotalMembers";

	/* Schema for MemberTable */
	/* Table Name */
	private static final String MemberTable = "MemberTable";
	/* Column Names */
	private static final String MemberTableTitle = "MemberTableTitle";
	private static final String MemberTableMember = "MemberTableMember";
	private static final String MemberTableAmount = "MemberTableAmount";
	private static final String MemberTableToGet = "MemberTableToGet";
	private static final String MemberTableToGive = "MemberTableToGive";

	/* Scheme for PurposeTable */
	/* Table Name */
	private static final String PurposeTable = "PurposeTable";
	/* Column Names */
	private static final String PurposeTableTitle = "PurposeTableTitle";
	private static final String PurposeTablePayingMember = "PurposeTablePayingMember";
	private static final String PurposeTablePurpose = "PurposeTablePurpose";
	private static final String PurposeTableAmount = "PurposeTableAmount";

	/* Table Name */
	private static final String UniqueNamesTable = "UniqueNamesTable";
	/* Column Names */
	private static final String UniqueNamesTableNames = "UniqueNamesTableNames";

	private DatabaseOpenHelper databaseOpenHelper;
	private Context context;
	private SQLiteDatabase sqliteDatabase;

	public static class DatabaseOpenHelper extends SQLiteOpenHelper {

		public DatabaseOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			/* Creating the AmountTable table */
			String amountTable = "CREATE TABLE " + AmountTable + " ("
					+ AmountTableTitle + " TEXT PRIMARY KEY , "
					+ AmountTableAmount + " INTEGER NOT NULL,"
					+ AmountTableTotalMembers + " TEXT NOT NULL );";
			db.execSQL(amountTable);

			/* Creating the MemberTable table */
			String memberTable = "CREATE TABLE MemberTable ("
					+ "MemberTableTitle TEXT NOT NULL, "
					+ "MemberTableMember TEXT NOT NULL , "
					+ "MemberTableAmount INTEGER DEFAULT 0 , "
					+ "MemberTableToGet INTEGER DEFAULT 0 , "
					+ "MemberTableToGive INTEGER DEFAULT 0 , "
					+ "FOREIGN KEY(MemberTableTitle) REFERENCES AmountTable(AmountTableTitle) on delete cascade on update cascade,"
					+ "primary key(MemberTableTitle,MemberTableMember));";
			db.execSQL(memberTable);

			/* Creating the PurposeTable table */
			String purposeTable = "CREATE TABLE PurposeTable ("
					+ "PurposeTableTitle TEXT NOT NULL,"
					+ "PurposeTablePayingMember TEXT NOT NULL, "
					+ "PurposeTablePurpose TEXT NOT NULL,"
					+ "PurposeTableAmount INTEGER NOT NULL, "
					+ "foreign KEY(PurposeTableTitle) REFERENCES AmountTable(AmountTableTitle) on delete cascade on update cascade, "
					+ "primary key(PurposeTableTitle,PurposeTablePayingMember,PurposeTablePurpose));";
			db.execSQL(purposeTable);
			
			String uniqueNamesTable = "CREATE TABLE " + UniqueNamesTable + " ("
					+ UniqueNamesTableNames + " TEXT PRIMARY KEY   );";
			db.execSQL(uniqueNamesTable);
			/*
			 * Failed Cases:Reason for making the Composite key: One person
			 * cannot pay for the same purpose two times for a given topic. For
			 * that he will have to delete the previous first. Again for
			 * different topics one person can pay for he same topic name two
			 * times
			 */
			/*
			 * Successful cases: So different people for the same purpose is
			 * possible because sometimes two people may give the entire amount
			 * on behalf of all four people
			 */
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			String uniqueNamesTable = "CREATE TABLE IF NOT EXISTS " + UniqueNamesTable + " ("
					+ UniqueNamesTableNames + " TEXT PRIMARY KEY   );";
			db.execSQL(uniqueNamesTable);

		}

	}

	/*
	 * Constructor to initialise the context because context is the activity
	 * which is passed as "this" from that class
	 */
	public DatabaseOperations_NonActivity(Context context) {
		this.context = context;
	}

	/* Create or Open a Database */
	public void openWriteableDatabase() {
		databaseOpenHelper = new DatabaseOpenHelper(context, DATABASE_NAME,
				null, DATABASE_VERSION);
		sqliteDatabase = databaseOpenHelper.getWritableDatabase();
		sqliteDatabase.execSQL("PRAGMA foreign_keys=ON");
	}

	/* Insert a particular record */
	public long insertIntoTable(String table, String[] columns,
			ArrayList<String> attributes) {
		ContentValues cv = new ContentValues();
		int i = 0, size = attributes.size();

		for (i = 0; i < size; i++)
			cv.put(columns[i], attributes.get(i).replaceAll("\\s+", " ").trim());

		return sqliteDatabase.insert(table, null, cv);
	}

	/* Select a particular record */
	/* Give only one column */
	public String retrieve(String table, String[] columns, String selection,
			String[] selectionArgs, String groupBy, String having,
			String orderBy) {

		Cursor c = sqliteDatabase.query(table, columns, selection,
				selectionArgs, groupBy, having, orderBy);
		if (c.moveToFirst()) {
			String name = c.getString(0);
			return name;
		}
		return null;
	}
	
	/* Select all records */
	public ArrayList<String> retrieveAll(String tableName, String[] columns,
			String where, String[] selectionArgs, String groupBy,
			String having, String orderBy) {
		ArrayList<String> names = new ArrayList<String>();
		Cursor c = sqliteDatabase.query(tableName, columns, where,
				selectionArgs, groupBy, having, orderBy);

		c.moveToFirst();
		if (c.getCount() == 0)
			return names;
		names.add(c.getString(0));
		while (c.moveToNext())
			names.add(c.getString(0));

		return names;
	}

	/* Update Operation */
	public int updateRow(String tableName, String columnName, String value,
			String where, String[] whereArgs) {
		ContentValues cv = new ContentValues();
		cv.put(columnName, value.replaceAll("\\s+", " ").trim());
		return sqliteDatabase.update(tableName, cv, where, whereArgs);
	}

	/* Update more than one column */
	public int updateMoreThanOneColumn(String tableName, String[] columnName,
			String[] value, String where, String[] whereArgs) {
		ContentValues cv = new ContentValues();
		for (int i = 0; i < columnName.length; i++)
			cv.put(columnName[i], value[i].replaceAll("\\s+", " ").trim());
		return sqliteDatabase.update(tableName, cv, where, whereArgs);
	}

	/* Delete Operation */
	public int deleteRow(String tableName, String where, String[] whereArgs) {
		return sqliteDatabase.delete(tableName, where, whereArgs);
	}

	public void closeDatabase() {
		databaseOpenHelper.close();
	}

	public static String getDatabaseName() {
		return DATABASE_NAME;
	}

	public static int getDatabaseVersion() {
		return DATABASE_VERSION;
	}

	public static String getAmounttable() {
		return AmountTable;
	}

	public static String getAmounttabletitle() {
		return AmountTableTitle;
	}

	public static String getAmounttableamount() {
		return AmountTableAmount;
	}

	public static String getMembertable() {
		return MemberTable;
	}

	public static String getMembertabletitle() {
		return MemberTableTitle;
	}

	public static String getMembertablemember() {
		return MemberTableMember;
	}

	public static String getPurposetable() {
		return PurposeTable;
	}

	public static String getPurposetabletitle() {
		return PurposeTableTitle;
	}

	public static String getPurposetablepayingmember() {
		return PurposeTablePayingMember;
	}

	public static String getPurposetablepurpose() {
		return PurposeTablePurpose;
	}

	public static String getPurposetableamount() {
		return PurposeTableAmount;
	}

	public static String getMembertableamount() {
		return MemberTableAmount;
	}

	public static String getAmounttabletotalmembers() {
		return AmountTableTotalMembers;
	}

	public static String getMembertabletoget() {
		return MemberTableToGet;
	}

	public static String getMembertabletogive() {
		return MemberTableToGive;
	}
	
	public static String getUniquenamestable() {
		return UniqueNamesTable;
	}

	public static String getUniquenamestablenames() {
		return UniqueNamesTableNames;
	}

}
